﻿using Sunboxsoft.Common;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace Sunboxsoft.ASMS.Client.Domain
{
    /// <summary>
    /// sqlite3 数据库文件初始化器
    /// </summary>
    public class SQLiteDatabase
    {
        public static string FilePath{get;private set;}
        public static string ConnectionString { get; set; }
        private string _filePath;
        private string _version = "1_0_0";

        private string LogTag = "sqlite";

        public SQLiteDatabase(string directory)
        {
            _filePath = string.Format("{0}asms_{1}.db", directory, _version);
            Logger.InfoToTag(LogTag, string.Format("constract sqlite db initiator, database file path:\"{0}\"", _filePath));
            FilePath = _filePath;
            ConnectionString = string.Format("Data Source={0}", FilePath);
        }

        public bool Init()
        {
            if (System.IO.File.Exists(_filePath))
            {
                Logger.InfoToTag(LogTag, string.Format("database file path:\"{0}\" is exist", _filePath));
                return true;
            }
            // 创建数据文件
            SQLiteConnection.ClearAllPools();
            SQLiteConnection.CreateFile(_filePath);
            SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0}", _filePath));

            bool result = true;
            try
            {
                conn.Open();
                Logger.InfoToTag(LogTag, "open database success");
            }
            catch (Exception ex)
            {
                System.IO.File.Delete(_filePath);
                Logger.ErrorToTag(LogTag, string.Format("database error:{0}", ex));
                result = false;
            }

            if (result)
            {
                result = CreateConfigTable(conn);
            }
            //if (result)
            //{
            //    result = InsertIntoConfigTable(conn);
            //}
            if (result)
            {
                result = CreateMasterDataTable(conn);
            } 
            if (result)
            {
                result = CreateMasterDataItemTable(conn);
            }
            if (result == false && conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
                // 删除数据库文件
                System.IO.File.Delete(_filePath);
            }
            return result;
        }

        private bool CreateConfigTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = 
@"create table ASMS_Config (
[Key] nvarchar(256) primary key not null,
[Value] nvarchar(256) not null)";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [ASMS_Config] error,ex:{0}", ex));
                return false;
            }
        }

        private bool CreateMasterDataTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText =
@"create table ASMS_MasterData (
[Code] nvarchar(32) primary key not null,
[Name] nvarchar(64) not null,
[Pinyin] nvarchar(64) not null,
[Letter] nvarchar(16) not null,
[Changeset] integer not null)";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [ASMS_MasterData] error,ex:{0}", ex));
                return false;
            }
        }

        private bool CreateMasterDataItemTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText =
@"create table ASMS_MasterDataItem (
[Code] nvarchar(32) primary key not null,
[MasterDataCode] nvarchar(32) not null,
[Name] nvarchar(64) not null,
[ParentCode] nvarchar(32),
[Pinyin] nvarchar(64) not null,
[Letter] nvarchar(16) not null,
[SortIndex] integer not null,
[Tag] integer)";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [ASMS_MasterDataItem] error,ex:{0}", ex));
                return false;
            }
        }

        private bool InsertIntoConfigTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmdInsertData0 = new SQLiteCommand();
                cmdInsertData0.CommandText = "insert into apms_config ([Key],[Value]) values ('RTServer.IP','127.0.0.1')";
                cmdInsertData0.Connection = conn;
                cmdInsertData0.ExecuteNonQuery();

                SQLiteCommand cmdInsertData1 = new SQLiteCommand();
                cmdInsertData1.CommandText = "insert into apms_config ([Key],[Value]) values ('RTServer.Port','4788')";
                cmdInsertData1.Connection = conn;
                cmdInsertData1.ExecuteNonQuery();

                SQLiteCommand cmdInsertData2 = new SQLiteCommand();
                cmdInsertData2.CommandText = "insert into apms_config ([Key],[Value]) values ('SYS.PositionCode','P0101010201')";
                cmdInsertData2.Connection = conn;
                cmdInsertData2.ExecuteNonQuery();

                SQLiteCommand cmdInsertData3 = new SQLiteCommand();
                cmdInsertData3.CommandText = "insert into apms_config ([Key],[Value]) values ('Sync.BeginTime','22:00')";
                cmdInsertData3.Connection = conn;
                cmdInsertData3.ExecuteNonQuery();

                SQLiteCommand cmdInsertData4 = new SQLiteCommand();
                cmdInsertData4.CommandText = "insert into apms_config ([Key],[Value]) values ('Sync.EndTime','23:00')";
                cmdInsertData4.Connection = conn;
                cmdInsertData4.ExecuteNonQuery();

                return true;
            }
            catch(Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("insert into table [apms_config] error,ex:{0}", ex));
                return false;
            }
        }

        private bool CreateStrategyBatchTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = "create table apms_strategyBatch ([Id] nvarchar(32) primary key not null, [Date] nvarchar(16) not null, [CreatedTime] nvarchar(32))";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [apms_strategy] error,ex:{0}", ex));
                return false;
            }
        }

        private bool CreateStrategyTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = @"create table apms_strategy (
[Id] nvarchar(32) primary key not null, 
[BatchId] nvarchar(32) not null,
[Name] nvarchar(64) not null, 
[BeginTime] nvarchar(16), 
[EndTime] nvarchar(16), 
[PositionCode] nvarchar(128),
[Priority] integer)";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [apms_strategy] error,ex:{0}", ex));
                return false;
            }
        }

        private bool CreateStrategyItemTable(SQLiteConnection conn)
        {
            return true;
            //try
            //{
            //    SQLiteCommand cmd = new SQLiteCommand();
            //    cmd.CommandText = "create table apms_strategyItem ([StrategyId] nvarchar(32) primary key not null, [AdvertisementId] nvarchar(32) not null, [Repeat] integer,[SortIndex] integer)";
            //    cmd.Connection = conn;
            //    cmd.ExecuteNonQuery();
            //    return true;
            //}
            //catch (Exception ex)
            //{
            //    Logger.ErrorToTag("sqlite", string.Format("create table [apms_strategyItem] error,ex:{0}", ex));
            //    return false;
            //}
        }

        private bool CreateAdvertisementTable(SQLiteConnection conn)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = @"create table apms_advertisement (
[Id] nvarchar(32) primary key not null,
[AdvertisementId] nvarchar(32) not null,
[Name] nvarchar(64) not null,
[StrategyId] nvarchar(32),
[Resource1Id] nvarchar(32),
[Resource2Id] nvarchar(32), 
[Resource3Id] nvarchar(32),
[Resource1Path] nvarchar(256), 
[Resource2Path] nvarchar(256), 
[Resource3Path] nvarchar(256), 
[Resource1Extension] nvarchar(32), 
[Resource2Extension] nvarchar(32), 
[Resource3Extension] nvarchar(32), 
[Duration] integer,
[Type] integer, 
[SortIndex] integer,
[Repeat] integer)";
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag(LogTag, string.Format("create table [apms_advertisement] error,ex:{0}", ex));
                return false;
            }
        }

        public static void BuildSQLCondition(SQLiteCommand command, string selectPart, Dictionary<string, object> paramDict, string orderBy = null)
        {
            bool isFirstParameter = true;
            StringBuilder builder = new StringBuilder(selectPart);
            builder.Append(" where");
            foreach (string key in paramDict.Keys)
            {
                if (isFirstParameter)
                {
                    builder.AppendFormat(" {0}=@{0}", key);
                    isFirstParameter = false;
                }
                else
                {
                    builder.AppendFormat(" and {0}=@{0}", key);
                }

                SQLiteParameter param = command.CreateParameter();
                param.ParameterName = string.Format("@{0}", key);
                param.Value = paramDict[key];
                command.Parameters.Add(param);
            }

            if (!string.IsNullOrEmpty(orderBy))
            {
                builder.AppendFormat(" {0}", orderBy);
            }
            command.CommandText = builder.ToString();
        }
    }
}
